libname lyof "C:\Users\Public\Documents\Results\Layoffs";

proc contents data=lyof.lyof03_04;
run;


proc sql; create table lyof.lyof03_04 as select 
			MIN(SIRET) as est,
			MIN(YEAR) as MINYEAR,
			MAX(YEAR) as MAXYEAR,
			SUM(pds_strat*(year="2003")) as nbexits03,
			SUM(nbexits*(year="2003")) as nbexits03,
			SUM(nblayoffs*(year="2003")) as nblayoffs03,
			SUM(nbotherlayoffs*(year="2003")) as nbothlayoffs03,
			SUM(nbexits*(year="2004")) as nbexits04,
			SUM(nblayoffs*(year="2004")) as nblayoffs04,
			SUM(nbotherlayoffs*(year="2004")) as nbothlayoffs04,
			MAX(effref*(year="2003")) as effref03,
			MAX(effref*(year="2004")) as effref04
			from lyof.layoffs (where=(year in ("2003","2004"))) group by siret;
		quit;



proc sql; create table lyof.lyof06_07 as select 
			MIN(SIRET) as est,
			MIN(YEAR) as MINYEAR,
			MAX(YEAR) as MAXYEAR,
			SUM(nbexits*(year="2006")) as nbexits06,
			SUM(nblayoffs*(year="2006")) as nblayoffs06,
			SUM(nbotherlayoffs*(year="2006")) as nbothlayoffs06,
			SUM(nbexits*(year="2007")) as nbexits07,
			SUM(nblayoffs*(year="2007")) as nblayoffs07,
			SUM(nbotherlayoffs*(year="2007")) as nbothlayoffs07,
			MAX(effref*(year="2006")) as effref06,
			MAX(effref*(year="2007")) as effref07
			from lyof.layoffs (where=(year in ("2006","2007"))) group by siret;
		quit;


proc sql; create table lyof.lyof09_10 as select 
			MIN(SIRET) as est,
			MIN(YEAR) as MINYEAR,
			MAX(YEAR) as MAXYEAR,
			SUM(nbexits*(year="2009")) as nbexits09,
			SUM(nblayoffs*(year="2009")) as nblayoffs09,
			SUM(nbotherlayoffs*(year="2009")) as nbothlayoffs09,
			SUM(nbexits*(year="2010")) as nbexits10,
			SUM(nblayoffs*(year="2010")) as nblayoffs10,
			SUM(nbotherlayoffs*(year="2010")) as nbothlayoffs10,
			MAX(effref*(year="2009")) as effref09,
			MAX(effref*(year="2010")) as effref10
			from lyof.layoffs (where=(year in ("2009","2010"))) group by siret;
		quit;

proc sql; create table lyof.lyof12_13 as select 
			MIN(SIRET) as est,
			MIN(YEAR) as MINYEAR,
			MAX(YEAR) as MAXYEAR,
			SUM(nbexits*(year="2012")) as nbexits12,
			SUM(nblayoffs*(year="2012")) as nblayoffs12,
			SUM(nbotherlayoffs*(year="2012")) as nbothlayoffs12,
			SUM(nbexits*(year="2013")) as nbexits13,
			SUM(nblayoffs*(year="2013")) as nblayoffs13,
			SUM(nbotherlayoffs*(year="2013")) as nbothlayoffs13,
			MAX(effref*(year="2012")) as effref12,
			MAX(effref*(year="2013")) as effref13
			from lyof.layoffs (where=(year in ("2012","2013"))) group by siret;
		quit;

proc sql; create table lyof.lyof15_16 as select 
			MIN(SIRET) as est,
			MIN(YEAR) as MINYEAR,
			MAX(YEAR) as MAXYEAR,
			SUM(nbexits*(year="2015")) as nbexits15,
			SUM(nblayoffs*(year="2015")) as nblayoffs15,
			SUM(nbotherlayoffs*(year="2015")) as nbothlayoffs15,
			SUM(nbexits*(year="2016")) as nbexits16,
			SUM(nblayoffs*(year="2016")) as nblayoffs16,
			SUM(nbotherlayoffs*(year="2016")) as nbothlayoffs16,
			MAX(effref*(year="2015")) as effref15,
			MAX(effref*(year="2016")) as effref16
			from lyof.layoffs (where=(year in ("2015","2016"))) group by siret;
		quit;


proc sql; create table lyof.lyof09_11 as select 
			MIN(SIRET) as est,
			MIN(YEAR) as MINYEAR,
			MAX(YEAR) as MAXYEAR,
			SUM(nbexits*(year="2009")) as nbexits09,
			SUM(nblayoffs*(year="2009")) as nblayoffs09,
			SUM(nbotherlayoffs*(year="2009")) as nbothlayoffs09,
			SUM(nbexits*(year="2010")) as nbexits10,
			SUM(nblayoffs*(year="2010")) as nblayoffs10,
			SUM(nbotherlayoffs*(year="2010")) as nbothlayoffs10,
			SUM(nbexits*(year="2011")) as nbexits11,
			SUM(nblayoffs*(year="2011")) as nblayoffs11,
			SUM(nbotherlayoffs*(year="2011")) as nbothlayoffs11,
			MAX(effref*(year="2009")) as effref09,
			MAX(effref*(year="2010")) as effref10,
			MAX(effref*(year="2011")) as effref11
			from lyof.layoffs (where=(year in ("2009","2010"))) group by siret;
		quit;
